package com.hnas.common.domain;

import java.util.List;

import com.hnas.core.util.StringUtil;



/**
 * 公共的sql语句
 * @author Administrator
 *
 */
public class CommonSQL {
	/**
	 * 构造12月份明细数据
	 * @param months
	 * @return
	 */
	public static String getM_TRAN_NOR_SETT_DATE_SQL(List<String> months,String userRole,boolean isOld){
		StringBuffer sb = new StringBuffer();
          for(String month:months){
        	  if(isOld){
        		  month="99";
        	  }
        	  sb.append(" UNION");
        	  sb.append(" SELECT");
        	  sb.append(" TRAN_SNO,");
			  sb.append("PKG_ID,");
			  sb.append("MRCH_ID,");
			  sb.append("EQP_ID,");
			  sb.append("EQP_SNO,");
			  sb.append("RECH_CHANNEL,");
			  sb.append("CARD_ID,");
			  sb.append("(SELECT DECODE(CUI.CARD_MARK,'1',CUI.CARD_AMT,'2',0) FROM M_CARD_USER_INFO CUI WHERE CUI.CARD_ID = T"+month+".CARD_ID) AS DEPT_AMT,");
			  sb.append("(SELECT DECODE(CUI.CARD_MARK,'1',0,'2',CUI.CARD_AMT) FROM M_CARD_USER_INFO CUI WHERE CUI.CARD_ID = T"+month+".CARD_ID) AS COST_AMT,");
			  sb.append("(SELECT CARD_KIND_NAME FROM M_CARD_KIND_INFO WHERE CARD_KIND_ID = T"+month+".CARD_TYPE) AS CARD_TYPE,");
			  sb.append("(CASE T"+month+".BATCH_NO WHEN '999988' THEN '远程扣款' ELSE (SELECT TRAN_TYPE_NAME FROM M_TRAN_TYPE WHERE TRAN_TYPE = T"+month+".TRAN_TYPE AND TRAN_SUB_TYPE = T"+month+".TRAN_SUB_TYPE) END) AS TRAN_TYPE,");
			  sb.append("(SELECT TRAN_SUB_TYPE_NAME FROM M_TRAN_TYPE WHERE TRAN_TYPE = T"+month+".TRAN_TYPE AND TRAN_SUB_TYPE = T"+month+".TRAN_SUB_TYPE) AS TRAN_SUB_TYPE,");
			  sb.append("TRAN_DATE,");
			  sb.append("TRAN_TIME,");
			  sb.append("REG_TIME,");
			  sb.append("SETTLE_DATE,");
			  sb.append("PSAM_ID,");
			  sb.append("PSAM_SEQ,");
			  sb.append("TRAN_AMT,");
			  sb.append("TRAN_VALUE,");
			  sb.append("CARD_BALANCE,");
			  sb.append("CARD_SEQ,");
			  sb.append("SUB_ID,");
			  sb.append("TAC_CODE,");
			  sb.append("TELLER,");
			  sb.append("CITY_CODE,");
			  sb.append("BATCH_NO,");
			  sb.append("(CASE T"+month+".BATCH_NO WHEN '999988' THEN '98' ELSE T"+month+".TRAN_TYPE END)AS TRAN_TYPE_ID,");
			  sb.append("TRAN_SUB_TYPE AS TRAN_SUB_TYPE_ID,");
			  sb.append("CORRECT");
			  sb.append(" FROM");
			  sb.append(" M_TRAN_NOR_SETT_DATE_"+month+" T"+month);
			  sb.append(" where 1=1 ");
			  //数据权限
			  if(StringUtil.isNotEmpty(userRole)){
				  sb.append("  and  SUB_ID in ("+StringUtil.getChangeString(userRole)+")");
			  }
          }
          String sql=sb.toString();
          if(StringUtil.isNotEmpty(sql)){
        	  sql=sql.substring(6);
          }
          return sql;
	}
	/**
	 * 餐补消费明细
	 * @param months
	 * @return
	 */
	public static String getSH_CBXFMX_T_SQL(List<String> months,String userRole,boolean isOld,boolean isIndustry){
		StringBuffer sb = new StringBuffer();
		String sqlTranSeft=getM_TRAN_NOR_SETT_DATE_SQL(months,userRole,isOld);
       sb.append(" SELECT"); 
       sb.append(" V.TRAN_SNO,");
       if(isOld){
    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }else{
    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '36' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }
       sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_AMT*-1 / 100  WHEN '32' THEN V.TRAN_AMT*-1 / 100  WHEN '37' THEN V.TRAN_AMT*-1 / 100 WHEN '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       sb.append("V.TRAN_TYPE_ID,");
       sb.append("V.TRAN_TYPE,");
       sb.append("V.CORRECT,");
       sb.append("V.CARD_ID,");
       sb.append("D.EMPLOYEE_ID,");
       sb.append("E.EMPLOYEE_NAME,");
       sb.append("E.CMP_NAME,");
       sb.append("B.MRCH_SHORT_NAME,");
       sb.append("B.MRCH_ID,");
       sb.append("TO_CHAR(TO_DATE(V.TRAN_DATE || LPAD(V.TRAN_TIME, 6, '0'), 'YYYYMMDDHH24MISS') ,'YYYY-MM-DD HH24:MI:SS') TRAN_DATE,");
       sb.append("V.SETTLE_DATE SETTLE_DATE,");
       sb.append("V.EQP_ID,");
       sb.append("V.SUB_ID,");
       sb.append("V.CARD_BALANCE/100 CARD_BALANCE,");
       sb.append("V.REG_TIME,");
       sb.append("V.CARD_TYPE");
       sb.append(" FROM ("+sqlTranSeft+") V");
       sb.append(" LEFT JOIN M_MRCH_BASIC_INFO B ON V.MRCH_ID = B.MRCH_ID");
       sb.append(" LEFT JOIN m_base_employee_card D ON V.CARD_ID=D.CARD_NO");
       sb.append(" LEFT JOIN  m_base_info_employeeinfo E ON E.EMPLOYEE_ID=D.EMPLOYEE_ID");
       sb.append(" where 1=1 ");
       //按行业查询
       if(isIndustry){
    	   //天航餐补,海航餐补,运营总部
    	   sb.append("  and  B.INDUSTRY_ID in ('8a95a14d57a7fc1a0157a85199fc000a','8a95a14d56e965fb0156f948cb9c0032','8afba18f57d7d4d00157dafe3f9d0880')");
       }
       
	   String sql=sb.toString();
	   return sql;
	}
	/**
	 * 构造12月份明细数据
	 * @param months
	 * @return
	 */
	public static String getM_TRAN_NOR_SETT_DATE_APP_SQL(String month,String userRole,boolean isOld){
		StringBuffer sb = new StringBuffer();
        	  sb.append(" SELECT");
        	  sb.append(" TRAN_SNO,");
			  sb.append("PKG_ID,");
			  sb.append("MRCH_ID,");
			  sb.append("EQP_ID,");
			  sb.append("EQP_SNO,");
			  sb.append("RECH_CHANNEL,");
			  sb.append("CARD_ID,");
			  sb.append("(SELECT DECODE(CUI.CARD_MARK,'1',CUI.CARD_AMT,'2',0) FROM M_CARD_USER_INFO CUI WHERE CUI.CARD_ID = T"+month+".CARD_ID) AS DEPT_AMT,");
			  sb.append("(SELECT DECODE(CUI.CARD_MARK,'1',0,'2',CUI.CARD_AMT) FROM M_CARD_USER_INFO CUI WHERE CUI.CARD_ID = T"+month+".CARD_ID) AS COST_AMT,");
			  sb.append("(SELECT CARD_KIND_NAME FROM M_CARD_KIND_INFO WHERE CARD_KIND_ID = T"+month+".CARD_TYPE) AS CARD_TYPE,");
			  sb.append("(SELECT TRAN_TYPE_NAME FROM M_TRAN_TYPE WHERE TRAN_TYPE = T"+month+".TRAN_TYPE AND TRAN_SUB_TYPE = T"+month+".TRAN_SUB_TYPE) AS TRAN_TYPE,");
			  sb.append("(SELECT TRAN_SUB_TYPE_NAME FROM M_TRAN_TYPE WHERE TRAN_TYPE = T"+month+".TRAN_TYPE AND TRAN_SUB_TYPE = T"+month+".TRAN_SUB_TYPE) AS TRAN_SUB_TYPE,");
			  sb.append("TRAN_DATE,");
			  sb.append("TRAN_TIME,");
			  sb.append("REG_TIME,");
			  sb.append("SETTLE_DATE,");
			  sb.append("PSAM_ID,");
			  sb.append("PSAM_SEQ,");
			  sb.append("TRAN_AMT,");
			  sb.append("TRAN_VALUE,");
			  sb.append("CARD_BALANCE,");
			  sb.append("CARD_SEQ,");
			  sb.append("SUB_ID,");
			  sb.append("TAC_CODE,");
			  sb.append("TELLER,");
			  sb.append("CITY_CODE,");
			  sb.append("BATCH_NO,");
			  sb.append("TRAN_TYPE AS TRAN_TYPE_ID,");
			  sb.append("TRAN_SUB_TYPE AS TRAN_SUB_TYPE_ID,");
			  sb.append("CORRECT");
			  sb.append(" FROM");
			  sb.append(" M_TRAN_NOR_SETT_DATE_"+month+" T"+month);
			  sb.append(" where 1=1 ");
			  //数据权限
			  if(StringUtil.isNotEmpty(userRole)){
				  sb.append("  and  SUB_ID in ("+StringUtil.getChangeString(userRole)+")");
			  }
         
          String sql=sb.toString();
          return sql;
	}
	/**
	 * 构造App新的查询语句,查核心交易记录
	 * @param months
	 * @return
	 */
	public static String getM_TRAN_NOR_SETT_DATE_APP_NEW_SQL(String month,String userRole,boolean isOld){
		String tempName = "T"+month;
		StringBuffer sb = new StringBuffer();
        	  sb.append(" SELECT ");
        	  sb.append(tempName).append(".TRAN_SNO,");
			  sb.append(tempName).append(".PKG_ID,");
			  sb.append(tempName).append(".MRCH_ID,");
			  sb.append(tempName).append(".EQP_ID,");
			  sb.append(tempName).append(".EQP_SNO,");
			  sb.append(tempName).append(".RECH_CHANNEL,");
			  sb.append(tempName).append(".CARD_ID,");
			  sb.append("(SELECT DECODE(CUI.CARD_MARK,'1',CUI.CARD_AMT,'2',0) FROM M_CARD_USER_INFO CUI WHERE CUI.CARD_ID = T"+month+".CARD_ID) AS DEPT_AMT,");
			  sb.append("(SELECT DECODE(CUI.CARD_MARK,'1',0,'2',CUI.CARD_AMT) FROM M_CARD_USER_INFO CUI WHERE CUI.CARD_ID = T"+month+".CARD_ID) AS COST_AMT,");
			  sb.append("(SELECT CARD_KIND_NAME FROM M_CARD_KIND_INFO WHERE CARD_KIND_ID = T"+month+".CARD_TYPE) AS CARD_TYPE,");
			  sb.append("(SELECT TRAN_TYPE_NAME FROM M_TRAN_TYPE WHERE TRAN_TYPE = T"+month+".TRAN_TYPE AND TRAN_SUB_TYPE = T"+month+".TRAN_SUB_TYPE) AS TRAN_TYPE,");
			  sb.append("(SELECT TRAN_SUB_TYPE_NAME FROM M_TRAN_TYPE WHERE TRAN_TYPE = T"+month+".TRAN_TYPE AND TRAN_SUB_TYPE = T"+month+".TRAN_SUB_TYPE) AS TRAN_SUB_TYPE,");
			  sb.append(tempName).append(".TRAN_DATE,");
			  sb.append(tempName).append(".TRAN_TIME,");
			  sb.append(tempName).append(".REG_TIME,");
			  sb.append(tempName).append(".SETTLE_DATE,");
			  sb.append(tempName).append(".PSAM_ID,");
			  sb.append(tempName).append(".PSAM_SEQ,");
			  sb.append(tempName).append(".TRAN_AMT,");
			  sb.append(tempName).append(".TRAN_VALUE,");
			  sb.append(tempName).append(".CARD_BALANCE,");
			  sb.append(tempName).append(".CARD_SEQ,");
			  sb.append(tempName).append(".SUB_ID,");
			  sb.append(tempName).append(".TAC_CODE,");
			  sb.append(tempName).append(".TELLER,");
			  sb.append(tempName).append(".CITY_CODE,");
			  sb.append(tempName).append(".BATCH_NO,");
			  sb.append(tempName).append(".TRAN_TYPE AS TRAN_TYPE_ID,");
			  sb.append(tempName).append(".TRAN_SUB_TYPE AS TRAN_SUB_TYPE_ID,");
			  sb.append(tempName).append(".CORRECT,");
			  sb.append("T.ORDER_NO,");
			  sb.append("T.ORDER_PRICE,");
			  sb.append("T.ORDER_STATUS,");
			  sb.append("T.MOBILE_PHONE,");
			  sb.append("T.OP_TELECOM_TYPE,");
			  sb.append("T.OP_PRODUCT_ID,");
			  sb.append("T.OP_PRODUCT_TYPE,");
			  sb.append("T.OP_PAY_PRICE");
			  sb.append(" FROM");
			  sb.append(" M_TRAN_NOR_SETT_DATE_"+month+" T"+month);
			  sb.append(" LEFT JOIN M_APP_TRANS T ON ").append(tempName).append(".TRAN_SNO = T.TRAN_SNO");
			  sb.append(" where 1=1 ");
			  //数据权限
			  if(StringUtil.isNotEmpty(userRole)){
				  sb.append("  and  SUB_ID in ("+StringUtil.getChangeString(userRole)+")");
			  }
         
          String sql=sb.toString();
          return sql;
	}
	
	/**
	 * 构造App新的查询语句,查核心交易记录
	 * @param months
	 * @return
	 */
	public static String getM_TRAN_NOR_SETT_DATE_APP_NEW_SQL3(String month,String userRole,boolean isOld){
		String tempName = "T"+month;
		StringBuffer sb = new StringBuffer();
        	  sb.append(" SELECT ");
        	  sb.append(tempName).append(".TRAN_SNO,");
			  sb.append(tempName).append(".PKG_ID,");
			  sb.append(tempName).append(".MRCH_ID,");
			  sb.append(tempName).append(".EQP_ID,");
			  sb.append(tempName).append(".EQP_SNO,");
			  sb.append(tempName).append(".RECH_CHANNEL,");
			  sb.append(tempName).append(".CARD_ID,");
			  sb.append(tempName).append(".TRAN_DATE,");
			  sb.append(tempName).append(".TRAN_TIME,");
			  sb.append(tempName).append(".REG_TIME,");
			  sb.append(tempName).append(".SETTLE_DATE,");
			  sb.append(tempName).append(".PSAM_ID,");
			  sb.append(tempName).append(".PSAM_SEQ,");
			  sb.append(tempName).append(".TRAN_AMT,");
			  sb.append(tempName).append(".TRAN_VALUE,");
			  sb.append(tempName).append(".CARD_BALANCE,");
			  sb.append(tempName).append(".CARD_SEQ,");
			  sb.append(tempName).append(".SUB_ID,");
			  sb.append(tempName).append(".TAC_CODE,");
			  sb.append(tempName).append(".TELLER,");
			  sb.append(tempName).append(".CITY_CODE,");
			  sb.append(tempName).append(".BATCH_NO,");
			  sb.append(tempName).append(".TRAN_TYPE AS TRAN_TYPE_ID,");
			  sb.append(tempName).append(".TRAN_SUB_TYPE AS TRAN_SUB_TYPE_ID,");
			  sb.append(tempName).append(".CORRECT,");
			  sb.append("T.ORDER_NO,");
			  sb.append("T.ORDER_PRICE,");
			  sb.append("T.ORDER_STATUS,");
			  sb.append("T.MOBILE_PHONE,");
			  sb.append("T.OP_TELECOM_TYPE,");
			  sb.append("T.OP_PRODUCT_ID,");
			  sb.append("T.OP_PRODUCT_TYPE,");
			  sb.append("T.OP_PAY_PRICE");
			  sb.append(" FROM");
//			  sb.append(" m_tran_nor_sett_date_app T"+month);
//			  sb.append(" LEFT JOIN M_APP_TRANS T ON ").append(tempName).append(".TRAN_SNO = T.TRAN_SNO");
			  sb.append(" m_tran_nor_sett_date_app T"+month);
			  sb.append(" LEFT JOIN M_APP_TRANS T ON ").append(tempName).append(".TRAN_SNO = T.TRAN_SNO");
			  sb.append(" where 1=1 ");
			  //数据权限
			  if(StringUtil.isNotEmpty(userRole)){
				  sb.append("  and  SUB_ID in ("+StringUtil.getChangeString(userRole)+")");
			  }
         
          String sql=sb.toString();
          return sql;
	}
	
	/**
	 * 餐补消费明细
	 * @param months
	 * @return
	 */
	public static String getSH_CBXFMX_T_APP_SQL(String months,String userRole,boolean isOld,boolean isIndustry){
		StringBuffer sb = new StringBuffer();
		String sqlTranSeft=getM_TRAN_NOR_SETT_DATE_APP_SQL(months,userRole,isOld);
       sb.append(" SELECT"); 
       sb.append(" V.TRAN_SNO,");
       if(isOld){
    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }else{
    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '36' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }
       sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_AMT*-1 / 100  WHEN '32' THEN V.TRAN_AMT*-1 / 100  WHEN '37' THEN V.TRAN_AMT*-1 / 100 WHEN '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       sb.append("V.TRAN_TYPE_ID,");
       sb.append("V.TRAN_TYPE,");
       sb.append("V.CORRECT,");
       sb.append("V.CARD_ID,");
       sb.append("D.EMPLOYEE_ID,");
       sb.append("E.EMPLOYEE_NAME,");
       sb.append("E.CMP_NAME,");
       sb.append("B.MRCH_SHORT_NAME,");
       sb.append("B.MRCH_ID,");
       sb.append("TO_CHAR(TO_DATE(V.TRAN_DATE || LPAD(V.TRAN_TIME, 6, '0'), 'YYYYMMDDHH24MISS') ,'YYYY-MM-DD HH24:MI:SS') TRAN_DATE,");
       sb.append("V.SETTLE_DATE SETTLE_DATE,");
       sb.append("V.EQP_ID,");
       sb.append("V.SUB_ID,");
       sb.append("V.CARD_BALANCE/100 CARD_BALANCE,");
       sb.append("V.REG_TIME,");
       sb.append("V.CARD_TYPE");
       sb.append(" FROM ("+sqlTranSeft+") V");
       sb.append(" LEFT JOIN M_MRCH_BASIC_INFO B ON V.MRCH_ID = B.MRCH_ID");
       sb.append(" LEFT JOIN m_base_employee_card D ON V.CARD_ID=D.CARD_NO");
       sb.append(" LEFT JOIN  m_base_info_employeeinfo E ON E.EMPLOYEE_ID=D.EMPLOYEE_ID");
       sb.append(" where 1=1 ");
       //按行业查询
       if(isIndustry){
    	   //天航餐补,海航餐补,运营总部
    	   sb.append("  and  B.INDUSTRY_ID in ('8a95a14d57a7fc1a0157a85199fc000a','8a95a14d56e965fb0156f948cb9c0032','8afba18f57d7d4d00157dafe3f9d0880')");
       }
       
	   String sql=sb.toString();
	   return sql;
	}	
	
	/**
	 * 餐补消费明细
	 * @param months
	 * @return
	 */
	public static String getSH_CBXFMX_T_APP_SQL3(String months,String userRole,boolean isOld,boolean isIndustry){
		StringBuffer sb = new StringBuffer();
		String sqlTranSeft=getM_TRAN_NOR_SETT_DATE_APP_SQL(months,userRole,isOld);
       sb.append(" SELECT"); 
       sb.append(" V.TRAN_SNO,");
       if(isOld){
    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }else{
    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '36' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }
       sb.append("(CASE V.TRAN_TYPE_ID WHEN '31' THEN V.TRAN_AMT*-1 / 100  WHEN '32' THEN V.TRAN_AMT*-1 / 100  WHEN '37' THEN V.TRAN_AMT*-1 / 100 WHEN '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       sb.append("V.TRAN_TYPE_ID,");
       sb.append("V.TRAN_TYPE,");
       sb.append("V.CORRECT,");
       sb.append("V.CARD_ID,");
//       sb.append("D.EMPLOYEE_ID,");
//       sb.append("E.EMPLOYEE_NAME,");
//       sb.append("E.CMP_NAME,");
       sb.append("B.MRCH_SHORT_NAME,");
       sb.append("B.MRCH_ID,");
       sb.append("TO_CHAR(TO_DATE(V.TRAN_DATE || LPAD(V.TRAN_TIME, 6, '0'), 'YYYYMMDDHH24MISS') ,'YYYY-MM-DD HH24:MI:SS') TRAN_DATE,");
       sb.append("V.SETTLE_DATE SETTLE_DATE,");
       sb.append("V.EQP_ID,");
       sb.append("V.SUB_ID,");
       sb.append("V.CARD_BALANCE/100 CARD_BALANCE,");
       sb.append("V.REG_TIME,");
       sb.append("V.CARD_TYPE");
       sb.append(" FROM ("+sqlTranSeft+") V");
       sb.append(" LEFT JOIN M_MRCH_BASIC_INFO B ON V.MRCH_ID = B.MRCH_ID");
//       sb.append(" LEFT JOIN m_base_employee_card D ON V.CARD_ID=D.CARD_NO");
//       sb.append(" LEFT JOIN  m_base_info_employeeinfo E ON E.EMPLOYEE_ID=D.EMPLOYEE_ID");
       sb.append(" where 1=1 ");
       //按行业查询
       if(isIndustry){
    	   //天航餐补,海航餐补,运营总部
    	   sb.append("  and  B.INDUSTRY_ID in ('8a95a14d57a7fc1a0157a85199fc000a','8a95a14d56e965fb0156f948cb9c0032','8afba18f57d7d4d00157dafe3f9d0880')");
       }
       
	   String sql=sb.toString();
	   return sql;
	}	
	
	/**
	 * 构造App新的查询语句(有关商充记录)查核心交易记录
	 */
	public static String getSH_CBXFMX_T_APP_NEW_SQL(String months,String userRole,boolean isOld,boolean isIndustry){
		StringBuffer sb = new StringBuffer();
		String sqlTranSeft=getM_TRAN_NOR_SETT_DATE_APP_NEW_SQL(months,userRole,isOld);
       sb.append(" SELECT"); 
       sb.append(" V.TRAN_SNO,");
//       if(isOld){
//    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '20' THEN V.TRAN_VALUE*-1 / 100 WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
//       }else{
//    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '20' THEN V.TRAN_VALUE*-1 / 100 WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '36' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
//       }
//       sb.append("(CASE V.TRAN_TYPE_ID WHEN '20' THEN V.TRAN_AMT*-1 / 100 WHEN '31' THEN V.TRAN_AMT*-1 / 100  WHEN '32' THEN V.TRAN_AMT*-1 / 100  WHEN '37' THEN V.TRAN_AMT*-1 / 100 WHEN '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       if(isOld){
    	   sb.append("(CASE WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '01' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '02' THEN V.TRAN_VALUE / 100 WHEN V.TRAN_TYPE_ID = '31' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '32' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '37' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }else{
    	   sb.append("(CASE WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '01' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '02' THEN V.TRAN_VALUE / 100 WHEN V.TRAN_TYPE_ID = '31' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '32' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '37' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '36' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }
       sb.append("(CASE WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '01' THEN V.TRAN_AMT*-1 / 100 WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '02' THEN V.TRAN_AMT / 100 WHEN V.TRAN_TYPE_ID = '31' THEN V.TRAN_AMT*-1 / 100  WHEN V.TRAN_TYPE_ID = '32' THEN V.TRAN_AMT*-1 / 100  WHEN V.TRAN_TYPE_ID = '37' THEN V.TRAN_AMT*-1 / 100 WHEN V.TRAN_TYPE_ID = '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       sb.append("V.TRAN_TYPE_ID,");
       sb.append("V.TRAN_TYPE,");
       sb.append("V.CORRECT,");
       sb.append("V.CARD_ID,");
       sb.append("D.EMPLOYEE_ID,");
       sb.append("E.EMPLOYEE_NAME,");
       sb.append("E.CMP_NAME,");
       sb.append("B.MRCH_SHORT_NAME,");
       sb.append("B.MRCH_ID,");
       sb.append("TO_CHAR(TO_DATE(V.TRAN_DATE || LPAD(V.TRAN_TIME, 6, '0'), 'YYYYMMDDHH24MISS') ,'YYYY-MM-DD HH24:MI:SS') TRAN_DATE,");
       sb.append("V.SETTLE_DATE SETTLE_DATE,");
       sb.append("V.EQP_ID,");
       sb.append("V.SUB_ID,");
       sb.append("V.CARD_BALANCE/100 CARD_BALANCE,");
       sb.append("V.REG_TIME,");
       sb.append("V.CARD_TYPE,");
	   sb.append("V.ORDER_NO,");
       sb.append("V.ORDER_PRICE,");
       sb.append("V.ORDER_STATUS,");
       sb.append("V.MOBILE_PHONE,");
       sb.append("V.OP_TELECOM_TYPE,");
       sb.append("V.OP_PRODUCT_ID,");
       sb.append("V.OP_PRODUCT_TYPE,");
       sb.append("V.OP_PAY_PRICE,");
       sb.append("V.TRAN_SUB_TYPE_ID,");
       sb.append("V.RECH_CHANNEL");
       sb.append(" FROM ("+sqlTranSeft+") V");
       sb.append(" LEFT JOIN M_MRCH_BASIC_INFO B ON V.MRCH_ID = B.MRCH_ID");
       sb.append(" LEFT JOIN m_base_employee_card D ON V.CARD_ID=D.CARD_NO");
       sb.append(" LEFT JOIN  m_base_info_employeeinfo E ON E.EMPLOYEE_ID=D.EMPLOYEE_ID");
       sb.append(" where 1=1 ");
       //按行业查询
       if(isIndustry){
    	   //天航餐补,海航餐补,运营总部
    	   sb.append("  and  B.INDUSTRY_ID in ('8a95a14d57a7fc1a0157a85199fc000a','8a95a14d56e965fb0156f948cb9c0032','8afba18f57d7d4d00157dafe3f9d0880')");
       }
       
	   String sql=sb.toString();
	   return sql;
	}	
	
	/**
	 * 构造App新的查询语句(有关商充记录)查核心交易记录
	 */
	public static String getSH_CBXFMX_T_APP_NEW_SQL3(String months,String userRole,boolean isOld,boolean isIndustry){
		StringBuffer sb = new StringBuffer();
		String sqlTranSeft=getM_TRAN_NOR_SETT_DATE_APP_NEW_SQL3(months,userRole,isOld);
       sb.append(" SELECT"); 
       sb.append(" V.TRAN_SNO,");
//       if(isOld){
//    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '20' THEN V.TRAN_VALUE*-1 / 100 WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
//       }else{
//    	   sb.append("(CASE V.TRAN_TYPE_ID WHEN '20' THEN V.TRAN_VALUE*-1 / 100 WHEN '31' THEN V.TRAN_VALUE*-1 / 100  WHEN '32' THEN V.TRAN_VALUE*-1 / 100  WHEN '37' THEN V.TRAN_VALUE*-1 / 100 WHEN '36' THEN V.TRAN_VALUE*-1 / 100 WHEN '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
//       }
//       sb.append("(CASE V.TRAN_TYPE_ID WHEN '20' THEN V.TRAN_AMT*-1 / 100 WHEN '31' THEN V.TRAN_AMT*-1 / 100  WHEN '32' THEN V.TRAN_AMT*-1 / 100  WHEN '37' THEN V.TRAN_AMT*-1 / 100 WHEN '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       if(isOld){
    	   sb.append("(CASE WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '01' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '02' THEN V.TRAN_VALUE / 100 WHEN V.TRAN_TYPE_ID = '31' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '32' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '37' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '11' THEN V.TRAN_VALUE / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }else{
    	   sb.append("(CASE WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '01' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '02' THEN V.TRAN_VALUE / 100 WHEN V.TRAN_TYPE_ID = '31' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '32' THEN V.TRAN_VALUE*-1 / 100  WHEN V.TRAN_TYPE_ID = '37' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '36' THEN V.TRAN_VALUE*-1 / 100 WHEN V.TRAN_TYPE_ID = '11' THEN (V.TRAN_VALUE+V.TRAN_AMT) / 100  else  V.TRAN_VALUE / 100  END) TRAN_VALUE,"); 
       }
       sb.append("(CASE WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '01' THEN V.TRAN_AMT*-1 / 100 WHEN V.TRAN_TYPE_ID = '20' AND V.TRAN_SUB_TYPE_ID = '02' THEN V.TRAN_AMT / 100 WHEN V.TRAN_TYPE_ID = '31' THEN V.TRAN_AMT*-1 / 100  WHEN V.TRAN_TYPE_ID = '32' THEN V.TRAN_AMT*-1 / 100  WHEN V.TRAN_TYPE_ID = '37' THEN V.TRAN_AMT*-1 / 100 WHEN V.TRAN_TYPE_ID = '36' THEN V.TRAN_AMT*-1 / 100 else  V.TRAN_AMT / 100 END) TRAN_AMT,");
       sb.append("V.TRAN_TYPE_ID,");
       sb.append("V.CORRECT,");
       sb.append("V.CARD_ID,");
       sb.append("B.MRCH_SHORT_NAME,");
       sb.append("B.MRCH_ID,");
       sb.append("TO_CHAR(TO_DATE(V.TRAN_DATE || LPAD(V.TRAN_TIME, 6, '0'), 'YYYYMMDDHH24MISS') ,'YYYY-MM-DD HH24:MI:SS') TRAN_DATE,");
       sb.append("V.SETTLE_DATE SETTLE_DATE,");
       sb.append("V.EQP_ID,");
       sb.append("V.SUB_ID,");
       sb.append("V.CARD_BALANCE/100 CARD_BALANCE,");
       sb.append("V.REG_TIME,");
	   sb.append("V.ORDER_NO,");
       sb.append("V.ORDER_PRICE,");
       sb.append("V.ORDER_STATUS,");
       sb.append("V.MOBILE_PHONE,");
       sb.append("V.OP_TELECOM_TYPE,");
       sb.append("V.OP_PRODUCT_ID,");
       sb.append("V.OP_PRODUCT_TYPE,");
       sb.append("V.OP_PAY_PRICE,");
       sb.append("V.TRAN_SUB_TYPE_ID,");
       sb.append("V.RECH_CHANNEL");
       sb.append(" FROM ("+sqlTranSeft+") V");
       sb.append(" LEFT JOIN M_MRCH_BASIC_INFO B ON V.MRCH_ID = B.MRCH_ID");
//       sb.append(" LEFT JOIN M_MRCH_BASIC_INFO B ON V.MRCH_ID = B.MRCH_ID");
       sb.append(" where 1=1 ");
       //按行业查询
       if(isIndustry){
    	   //天航餐补,海航餐补,运营总部
    	   sb.append("  and  B.INDUSTRY_ID in ('8a95a14d57a7fc1a0157a85199fc000a','8a95a14d56e965fb0156f948cb9c0032','8afba18f57d7d4d00157dafe3f9d0880')");
       }
       
	   String sql=sb.toString();
	   return sql;
	}	
	
	public static void main(String[] args) {
//		String sql = getM_TRAN_NOR_SETT_DATE_APP_NEW_SQL("03","224102",false);
//		String sql = getM_TRAN_NOR_SETT_DATE_APP_NEW_SQL("APP","224102",false);
//		String sql = getSH_CBXFMX_T_APP_NEW_SQL("03","224102",false,false);
		String sql = getSH_CBXFMX_T_APP_NEW_SQL("APP","224102",false,false);
		System.out.println(sql);
	}
}
